/***********************************************************************************************************************************
 *    Name:	Job_DatabaseBackup&Maintenance.sql
 *  Author: Frank Figearo — http://sqlnerd.me/ — frank@sqlnerd.me
 * Summary:	Backup database(s) and perform maintenance tasks.

 DANGER * DANGER * DANGER * DANGER * DANGER * DANGER * DANGER * DANGER * DANGER * DANGER * DANGER * DANGER * DANGER * DANGER * DANGER
 THESE JOBS MAY BE CUSTOMIZED FOR INDIVIDUAL SERVERS. TAKE CARE NOT TO OVERWRITE CUSTOMIZATIONS.

 */
USE msdb;
GO
IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name LIKE N'• %(Custom)') RAISERROR(N'Customized Job Found', 18, 1);

-- Database Backup & Maintenance ===================================================================================================
DECLARE
	@JobName			NVARCHAR(128),
	@WorkingDirectory	NVARCHAR(255),
	@OutputFileName		NVARCHAR(255),
	@subsystem			NVARCHAR(40),
	@command			NVARCHAR(MAX),
	@job_id				BINARY(16);

SET @JobName= N'• Database Backup & Maintenance';
SET	@WorkingDirectory= master.DBAdmin.GetInstancePath(N'WorkingDirectory');

SELECT @job_id= job_id FROM msdb.dbo.sysjobs WHERE name = @JobName;
IF @job_id IS Null BEGIN
	EXECUTE dbo.sp_add_job
		@job_name			= @JobName,
		@category_name		= N'DBAdmin',
		@owner_login_name	= N'sa',
		@job_id				= @job_id OUTPUT;
	EXECUTE dbo.sp_add_jobserver @job_id= @job_id, @server_name= N'(LOCAL)';
END; ELSE EXECUTE dbo.sp_delete_jobstep @job_id= @job_id, @step_id= 0;

EXECUTE dbo.sp_update_job
	@job_id						= @job_id,
--	@enabled					= 1,
	@notify_level_page			= 0,	-- Never
	@notify_level_netsend		= 0,	-- Never
	@notify_level_eventlog		= 0,	-- Never
	@notify_level_email			= 2,	-- On Failure
	@notify_email_operator_name	= N'(DB)A-Team';

SET @OutputFileName= @WorkingDirectory + N'BackupDBs.txt';
EXECUTE dbo.sp_add_jobstep
	@job_id				= @job_id,
	@step_name			= N'Backup All Databases',
	@on_success_action	= 3,	-- Go to Next Step
	@command			= N'PRINT @@SERVERNAME; EXECUTE DBAdmin.BackupDBs @hrs_limit= 312, @pct_limit= 33;',	-- 13 Days
	@output_file_name	= @OutputFileName;

SET @OutputFileName= @WorkingDirectory + N'CheckDBIntegrity.txt';
EXECUTE dbo.sp_add_jobstep
	@job_id				= @job_id,
	@step_name			= N'Check DB Integrity',
	@on_success_action	= 3,	-- Go to Next Step
	@command			= N'PRINT @@SERVERNAME; EXECUTE DBAdmin.CheckDBIntegrity;',
	@output_file_name	= @OutputFileName;

SET @OutputFileName= @WorkingDirectory + N'DefragmentIndexes.txt';
EXECUTE dbo.sp_add_jobstep
	@job_id				= @job_id,
	@step_name			= N'Defragment Indexes',
	@on_success_action	= 3,	-- Go to Next Step
	@command			= N'PRINT @@SERVERNAME; EXECUTE DBAdmin.DefragmentIndexes;',
	@retry_attempts		= 1,
	@retry_interval		= 1,
	@output_file_name	= @OutputFileName;

SET @OutputFileName= master.DBAdmin.GetInstancePath(N'BackupDirectory') + N'ServerLogins.sql';
EXECUTE dbo.sp_add_jobstep
	@job_id				= @job_id,
	@step_name			= N'Script Logins',
	@on_success_action	= 3,	-- Go to Next Step
	@command			= N'PRINT @@SERVERNAME; EXECUTE DBAdmin.ScriptLogins;',
	@output_file_name	= @OutputFileName;

SET @OutputFileName= @WorkingDirectory + N'Cleanup.txt';
IF 9 < master.DBAdmin.ProductVersion() BEGIN
SET @subsystem= N'PowerShell';
SET @command= REPLACE(N'Get-Date -Format T; Write-Output "$env:USERDOMAIN\$env:USERNAME";

# open the server connection
$cn= New-Object System.Data.SqlClient.SQLConnection("Data Source=(LOCAL);Integrated Security=True");
Write-Output $cn.ConnectionString;
$cn.Open();

# set/get configuration items
$FolderName= $Null;
$cmd= $cn.CreateCommand();
$cmd.CommandText= "SET NOCOUNT ON; CREATE TABLE #BackupFolder (BackupFile NVARCHAR(260) NOT Null PRIMARY KEY CLUSTERED); SELECT DBAdmin.GetInstancePath(''BackupDirectory'');";
$r= $cmd.ExecuteReader();
if ($r.read()) {$FolderName= $r[0]};
$r.Close();
Write-Output $FolderName;

# load the backups file list to the server
Set-Location "C:\";
foreach ($file in Get-ChildItem -Path $FolderName) {
  $FileName= $file.FullName;
  $cmd.CommandText= "INSERT INTO #BackupFolder VALUES (''$FileName'');";
  [void]$cmd.ExecuteNonQuery();
};

# fetch the subset of that list to delete
$PurgeList= @();
$cmd.CommandTimeout= 3600;	# 1 Hour
$cmd.CommandText= "EXECUTE DBAdmin.GetDBBackupPurgeList @retention= 720;";	# 30 day retention
Write-Output $cmd.CommandText;
$r= $cmd.ExecuteReader();
while ($r.read()) {$PurgeList+= $r[0]};
$r.Close(); $cn.Close();

# delete old files
if ($PurgeList.Count -gt 0) {Remove-Item -LiteralPath $PurgeList -ErrorAction SilentlyContinue -Verbose};
Write-Output $PurgeList.Count;
Get-Date -Format T;', N'(LOCAL)', @@SERVERNAME);
END; ELSE BEGIN
  SET @subsystem= N'CmdExec';
  SET @command= N'"' +  @WorkingDirectory + N'Cleanup.cmd' + N'"';
END;

EXECUTE dbo.sp_add_jobstep
	@job_id				= @job_id,
	@step_name			= N'Cleanup',
	@subsystem			= @subsystem,
	@command			= @command,
	@retry_attempts		= 1,
	@retry_interval		= 1,
	@output_file_name	= @OutputFileName;


BEGIN TRY;
  EXECUTE dbo.sp_attach_schedule @job_name= @JobName, @schedule_name= @JobName;
END TRY BEGIN CATCH END CATCH;
GO